using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;

namespace CommonTagTime
{
    public class clsSQL : clsConnection
    {
        public clsSQL() : base(enmDBType.MS_SQL)
        {
            _TotalRecords = 0;
        }

        public clsSQL(string ConnectingString) : base(Convert.ToByte(enmDBType.MS_SQL), ConnectingString)
        {
        }

        public clsSQL(string FileName, string DataBaseFolder) : base(FileName, DataBaseFolder)
        {
        }

        public clsSQL(string FileName, string DataBaseFolder, enmFileReadHeader ReadHeader) : base(FileName, DataBaseFolder, ReadHeader)
        {
        }

        public clsSQL(string ServerName, string DatabaseName, string UserName, string Password) : base(ServerName, DatabaseName, UserName, Password)
        {
        }

        public SqlDataReader SPDataReader(string StoreName)
        {
            SqlDataReader de;
            SqlCommand cmd = new SqlCommand(StoreName, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                de = cmd.ExecuteReader();
              
            }
            catch (Exception e)
            {
                de = null;
                clsFile.WriteLog("clsSQL", "_SPDataReader(string StoreName)", e.ToString());
            }
            finally
            {
                //if (cmd.Connection.State == ConnectionState.Open)
                //    cmd.Connection.Close();

                cmd.Dispose();
                cmd = null;
            }
            return de;
        }


     

        public SqlDataReader SPDataReader(string StoreName, SqlParameter[] Parameters)
        {
            SqlDataReader de;
            SqlConnection objConnent = (SqlConnection)base.Connection;
           // OpenConnection(objConnent);          
            SqlCommand cmd = new SqlCommand(StoreName,objConnent);            
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i <= (Parameters.Length - 1); i++)
            {
                cmd.Parameters.Add(Parameters[i]);
            }
           
            SqlParameter Para = new SqlParameter("@@ROWCOUNT", SqlDbType.Int);
            Para.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(Para);

            try
            {
                de = cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                de = null;
             //   clsFile.WriteLog("clsSQL", "_SPDataReader(string StoreName, SqlParameter[] Parameters)", e.ToString());
                throw e;
            }
            finally
            {
                //if (cmd.Connection.State == ConnectionState.Open)
                //    cmd.Connection.Close();                
                cmd.Dispose();
                cmd = null;
            }

          //  CloseConnection(objConnent);
            return de;
        }

        public DataTable SPDataTable(string StoreName)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dap = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(StoreName, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                dap.SelectCommand = cmd;
                dap.Fill(dt);

                this._TotalRecords = dt.Rows.Count;
            }
            catch (Exception e)
            {
                dt = null;
               // clsFile.WriteLog("clsSQL", "_SPDataTable(string StoreName)", e.ToString());
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();
                cmd = null;

                dap.Dispose();
                dap = null;
            }

            return dt;
        }

        public DataTable ExecDataTable(string sql)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dap = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(sql, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.Text;

            try
            {
                dap.SelectCommand = cmd;
                dap.Fill(dt);

                this._TotalRecords = dt.Rows.Count;
            }
            catch (Exception e)
            {
                dt = null;
                // clsFile.WriteLog("clsSQL", "_SPDataTable(string StoreName)", e.ToString());
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();
                cmd = null;

                dap.Dispose();
                dap = null;
            }

            return dt;
        }

        public DataTable SPDataTable(string StoreName, SqlParameter[] Parameters)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter dap = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(StoreName, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i <= (Parameters.Length - 1); i++)
            {
                cmd.Parameters.Add(Parameters[i]);
            }

            SqlParameter Para = new SqlParameter("@@ROWCOUNT", SqlDbType.Int);
            Para.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(Para);

            try
            {
                dap.SelectCommand = cmd;
                dap.Fill(dt);

                _TotalRecords = Convert.ToInt32(Para.Value);
             }
            catch (Exception e)
            {
                
                //clsFile.WriteLog("clsSQL", "_SPDataTable(string StoreName, SqlParameter[] Parameters)", e.ToString());
                dt = null;
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();
                cmd = null;

                dap.Dispose();
                dap = null;
            }

            return dt;
        }

        public int SPExecute(string StoreName, SqlParameter[] Parameters)
        {
            int returnValue=0;//default faild
            SqlCommand cmd = new SqlCommand(StoreName, (SqlConnection)base.Connection);
            
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i <= (Parameters.Length - 1); i++)
            {
                cmd.Parameters.Add(Parameters[i]);
            }
            //add return paramater
            // Return value as parameter
            SqlParameter returnParameters = new SqlParameter("returnVal", SqlDbType.Int);
            returnParameters.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(returnParameters);
            try
            {
                cmd.ExecuteNonQuery();//return row effect
                returnValue = Convert.ToInt32(returnParameters.Value);              
            }
            catch (Exception e)
            {
                //clsFile.WriteLog("clsSQL", "_SPExecute(string StoreName, SqlParameter[] Parameters)", e.ToString());                
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
                cmd.Dispose();
                cmd = null;
            }
            return returnValue;
        }

        public object ExecuteScalar(string Command)
        {
            object obj;
            SqlCommand cmd = new SqlCommand(Command, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.Text;

            try
            {
                obj = cmd.ExecuteScalar();
            }
            catch (Exception e)
            {          
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();
                cmd.Dispose();
                cmd = null;
            }

            return obj;
        }
      

        public DataSet SPDataSet(string StoreName, SqlParameter[] Parameters)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter dap = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(StoreName, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i <= (Parameters.Length - 1); i++)
            {
                cmd.Parameters.Add(Parameters[i]);
            }

            try
            {
                dap.SelectCommand = cmd;
                dap.Fill(ds);

                ds.Dispose();
            }
            catch (Exception e)
            {
              //  clsFile.WriteLog("clsSQL", "_SPDataSet(string StoreName, SqlParameter[] Parameters)", e.ToString());
                ds = null;
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();
                cmd = null;

                dap.Dispose();
                dap = null;
            }

            return ds;
        }

        public DataSet SPDataSet(string StoreName)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter dap = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(StoreName, (SqlConnection)base.Connection);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                dap.SelectCommand = cmd;
                dap.Fill(ds);        
            }
            catch (Exception e)
            {
               // clsFile.WriteLog("clsSQL", "_SPDataSet(string StoreName)", e.ToString());
                ds = null;
                throw e;
            }
            finally
            {
                if (cmd.Connection.State == ConnectionState.Open)
                    cmd.Connection.Close();

                cmd.Dispose();
                cmd = null;

                dap.Dispose();
                dap = null;
            }

            return ds;
        }

        public override void Dispose()
        {
            CloseConnection();
            base.Dispose();
        }

        public int TotalRecords
        {
            get
            {
                return this._TotalRecords;
            }
            set
            {
                this._TotalRecords = value;
            }
        }

        private int _TotalRecords;
       
        private void CloseConnection()
        {
            SqlConnection objConnect = (SqlConnection)base.Connection;
            if (objConnect.State == ConnectionState.Open)
                objConnect.Close();
        }
    }
}

